﻿using System;
using System.Collections.Generic;
using System.Data;
using br.com.SistemaChamados.Controller.Common;
using br.com.SistemaChamados.Enum;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.Model.Common;

namespace br.com.SistemaChamados.DAO
{
    public class CallDAO
    {
        private const string ID_STATUS_ROUTED_CALL = "2";
        private const string ID_STATUS_OPENED_CALL = "1";

        private static string SQL_GET_CALL                = "SELECT * FROM view_get_calls " +
                                                            "  WHERE call_id  = @callid";

        private static string SQL_INSERT_CALL             = "INSERT INTO TBL_CALL "+
                                                            "   (TBL_CALLID, TBL_CALLPROBLEMDESCRIPTION, TBL_USEROPEN, TBL_CALLSTATUSID, "+
                                                            "   TBL_CALLSUBCATEGORYID, TBL_SECTORID) " +
                                                            "VALUES "+
                                                            "   (@callid, @callprobdescription, @useropen, @callstatusid, "+
                                                            "   @callsubcategoryid, @sectorid) ";

        private static string SQL_GET_CALLID              = "SELECT * FROM FUN_GETCALLID() ";

        private static string SQL_SELECT_CALLS_USER       = "SELECT * FROM view_get_calls WHERE user_open_id=@useropenid";

        private static string SQL_SELECT_CALLS_TECHNICIAN = "SELECT * FROM view_get_calls "+
                                                            "   WHERE user_close_id=@usercloseid AND callstatus_id = "+ID_STATUS_ROUTED_CALL;

        private static string SQL_UPDATE_CALL             = "UPDATE TBL_CALL SET "+
                                                            "   TBL_CALLSOLUTIONDESCRIPTION=@callsolution, "+
                                                            "   TBL_USERCLOSE=@userclose, "+
                                                            "   TBL_CALLSTATUSID = @callstatusid, "+
                                                            "   TBL_CALLOBSERVATION = @callobservation "+
                                                            "WHERE TBL_CALLID=@callid ";

        private static string SQL_GET_CALLS               = "SELECT * FROM view_get_calls " +
                                                            "   WHERE callstatus_id = "+ID_STATUS_OPENED_CALL;

        public static EnumCRUDState UpdateCall(Call call)
        {
            EnumCRUDState stateTransaction = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_CALL, conn);
                command.Parameters.Add(Mapped.Parameter("@callsolution", call.SolutionDescription));
                command.Parameters.Add(Mapped.Parameter("@userclose", call.UserFinish.Id));
                command.Parameters.Add(Mapped.Parameter("@callobservation", call.Observation));
                command.Parameters.Add(Mapped.Parameter("@callstatusid", call.CallStatus.Id));
                command.Parameters.Add(Mapped.Parameter("@callid", call.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return stateTransaction;
        }

        public static Call GetCall(Call call)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Call resultCall = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALL, conn);
                command.Parameters.Add(Mapped.Parameter("@callid", call.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultCall = extractCallFromDataSet(reader);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return resultCall;
        }

        public static List<Call> GetCallsByUserOpenAndFilters(int userOpenId, int? callId, string dateOpening, string problemDescription, int? categoryId, int? subCategoryId, int? statusId)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            List<Call> listCall = new List<Call>();
            string searchSql = "";
            try
            {
                conn = Mapped.Connection();
                searchSql = createSqlFilterWithParams(callId, dateOpening, problemDescription, categoryId, subCategoryId, statusId);
                searchSql = searchSql + " ORDER BY call_id DESC";
                command = Mapped.Command(SQL_SELECT_CALLS_USER + searchSql, conn);
                command.Parameters.Add(Mapped.Parameter("@useropenid", userOpenId));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Call resultCall = extractCallFromDataSet(reader);
                    listCall.Add(resultCall);
                    resultCall = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return listCall;
        }

        public static List<Call> GetCallsByTechnician(int userCloseid, int? callId, string dateOpening, string problemDescription, int? categoryId, int? subCategoryId, int? statusId)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            List<Call> listCall = new List<Call>();
            string searchSql = "";
            try
            {
                conn = Mapped.Connection();
                searchSql = createSqlFilterWithParams(callId, dateOpening, problemDescription, categoryId, subCategoryId, statusId);
                searchSql = searchSql + " ORDER BY call_id DESC";
                command = Mapped.Command(SQL_SELECT_CALLS_TECHNICIAN + searchSql, conn);
                command.Parameters.Add(Mapped.Parameter("@usercloseid", userCloseid));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Call resultCall = extractCallFromDataSet(reader);
                    listCall.Add(resultCall);
                    resultCall = null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return listCall;
        }

        public static int? GetCallId()
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            int? callId = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLID, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    callId = Convert.ToInt32(reader["FUN_GETCALLID"]);
                }
                return callId;
            }
            catch (Exception ex)
            {
                return callId;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

        }

        public static EnumGenericState InsertCall(Call call)
        {
            EnumGenericState resultCall = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_CALL, conn);
                command.Parameters.Add(Mapped.Parameter("@callid", call.Id));
                command.Parameters.Add(Mapped.Parameter("@callprobdescription", call.ProblemDescription));
                command.Parameters.Add(Mapped.Parameter("@useropen", call.UserOpen.Id));
                command.Parameters.Add(Mapped.Parameter("@callstatusid", 1));
                command.Parameters.Add(Mapped.Parameter("@callsubcategoryid", call.CallSubCategory.Id));
                command.Parameters.Add(Mapped.Parameter("@sectorid", call.Sector.Id));
                command.ExecuteNonQuery();
                resultCall = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultCall = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return resultCall;
        }

        public static List<Call> getOpenCalls()
        {
            List<Call> list = new List<Call>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLS, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    Call callReturn = extractCallFromDataSet(reader);
                    list.Add(callReturn);
                    callReturn = null;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static List<Call> getOpenCallsWithFilters(int userOpenId, int? callId, string dateOpening, string problemDescription, int? categoryId, int? subCategoryId, int? statusId)
        {
            List<Call> list = new List<Call>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            try
            {
                conn = Mapped.Connection();
                string searchSql = "";
                searchSql = createSqlFilterWithParams(callId, dateOpening, problemDescription, categoryId, subCategoryId, statusId);
                searchSql = Convert.ToString(searchSql + " ORDER BY TBL_CALLID DESC");
                string filteredSql = SQL_GET_CALLS + searchSql;
                command = Mapped.Command(filteredSql, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    Call callReturn = extractCallFromDataSet(reader);
                    list.Add(callReturn);
                    callReturn = null;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        private static CallCategory extractCallCategoryFromResultSet(IDataReader reader)
        {
            CallCategory callCategory = new CallCategory();
            callCategory.Description = reader["category"].ToString();
            return callCategory;
        }

        private static CallSubCategory extractCallSubCategoryFromResultSet(IDataReader reader)
        {
            CallSubCategory callSubCategory = new CallSubCategory();
            callSubCategory.Description = reader["subcategory"].ToString();
            return callSubCategory;
        }

        private static CallStatus extractCallStatusFromResultSet(IDataReader reader)
        {
            CallStatus callStatus = new CallStatus();
            callStatus.Id = Convert.ToInt32(reader["callstatus_id"]);
            callStatus.Description = reader["status"].ToString();
            return callStatus;
        }

        private static Unit extractUnitFromResultSet(IDataReader reader)
        {
            Unit unit = new Unit();
            unit.Name = reader["user_open_unit"].ToString();
            return unit;
        }

        private static Sector extractSectorFromResultSet(IDataReader reader)
        {
            Sector sector = new Sector();
            sector.Description = reader["user_open_sector"].ToString();
            return sector;
        }

        private static User extractUserFromResultSet(IDataReader reader)
        {
            User userOpen = new User();
            UserController userController = new UserController();
            userOpen.Name = reader["user_open"].ToString();
            userOpen.EMail = reader["user_open_email"].ToString();
            userOpen = userController.getUserByNameAndEmail(userOpen.Name, userOpen.EMail);
            return userOpen;
        }

        private static User extractUserCloseFromResultSet(IDataReader reader)
        {
            UserController userController = new UserController();
            User userClose = new User();
            if (!reader["user_finish"].ToString().Equals("") &&
                !reader["user_finish_email"].ToString().Equals(""))
            {
                userClose.Name = reader["user_finish"].ToString();
                userClose.EMail = reader["user_finish_email"].ToString();
                userClose = userController.getUserByNameAndEmail(userClose.Name, userClose.EMail);
            }
            return userClose;
        }

        private static Call extractCallFromDataSet(IDataReader reader)
        {
            Call resultCall = new Call();
            resultCall.Id = Convert.ToInt32(reader["call_id"].ToString());
            resultCall.DateOpening = Convert.ToDateTime(reader["date_opening"]);
            if (!reader["date_closing"].ToString().Equals("")) resultCall.DateClosing = Convert.ToDateTime(reader["date_closing"]);
            resultCall.ProblemDescription = reader["problem_description"].ToString();
            resultCall.SolutionDescription = reader["problem_solution"].ToString();
            resultCall.UserOpen = extractUserFromResultSet(reader);
            Sector sector = extractSectorFromResultSet(reader);
            Unit unit = extractUnitFromResultSet(reader);
            resultCall.Sector = sector;
            resultCall.Sector.Unit = unit;
            User userClose = extractUserCloseFromResultSet(reader);
            if (userClose != null) resultCall.UserFinish = userClose;
            CallStatus callStatus = extractCallStatusFromResultSet(reader);
            resultCall.CallStatus = callStatus;
            CallSubCategory callSubCategory = extractCallSubCategoryFromResultSet(reader);
            CallCategory callCategory = extractCallCategoryFromResultSet(reader);
            callSubCategory.CallCategory = callCategory;
            resultCall.CallSubCategory = callSubCategory;
            Annex returnedAnnex = AnnexDAO.GetAnnexByCall(resultCall);
            List<Annex> listAnnex = new List<Annex>();
            if (returnedAnnex != null)
            {
                listAnnex.Add(returnedAnnex);
            }
            resultCall.Annex = listAnnex;
            return resultCall;
        }

        private static string createSqlFilterWithParams(int? callId, string dateOpening, string problemDescription, int? categoryId, int? subCategoryId, int? statusId)
        {
            string searchSql = "";
            if (callId != null)
            {
                searchSql = Convert.ToString(searchSql + " AND call_id = " + callId.Value);
            }
            if (dateOpening != null && !dateOpening.Equals(""))
            {
                searchSql = Convert.ToString(searchSql + "  AND date_opening BETWEEN '" + dateOpening + " 00:00:00' AND '" + dateOpening + " 23:59:59'");
            }
            if (problemDescription != null && !problemDescription.Equals(""))
            {
                searchSql = Convert.ToString(searchSql + " AND problem_description LIKE '%" + problemDescription + "%'");
            }
            if (categoryId != null && categoryId > 0)
            {
                searchSql = Convert.ToString(searchSql + " AND category_id = " + categoryId);
            }
            if (subCategoryId != null && subCategoryId > 0)
            {
                searchSql = Convert.ToString(searchSql + " AND subcategory_id = " + subCategoryId);
            }
            if (statusId != null && statusId > 0)
            {
                searchSql = Convert.ToString(searchSql + " AND callstatus_id = " + statusId);
            }
            return searchSql;
        }
    }
}